from openpyxl import Workbook
from openpyxl import load_workbook
from datetime import datetime
from os import path

from db_students import Student

class StudentsDB():

    def __init__(self, name_department, backup_1C = None):
        self.__now_date = datetime.now().timetuple()
        self.__name_department = name_department
        if backup_1C:
            self.load_1C_backup(backup_1C)
            self.__all_grps_name_count_stud = self.__get_all_groups()

    def load_1C_backup(self, db_backup_file):
        self.__backup_db_1C_workbook = load_workbook(db_backup_file)
        self.__ws = self.__backup_db_1C_workbook.active
        self.__parse_all_columns()
        self.__all_grps_name_count_stud = self.__get_all_groups()

    def get_name_columns(self):

        return self.__names_columns

    def __parse_all_columns(self):
        students = self.__students = list()
        all_values = self.__ws.values
        i = 0
        for row in all_values:
            i += 1
            if (i < 4):
                continue
            email = row[8]
            if (email == None):
                email = ''
            middle_name = row[3]
            if (middle_name == None):
                middle_name = ''
            students.append(
                Student.Student(
                    int(row[0]),
                    row[1],
                    row[2],
                    middle_name,
                    row[6],
                    row[7],
                    email
                )
            )
    
    def __get_all_students(self):
        return self.__students
    
    def __get_all_groups(self, need_year_enrl = None):
        all_groups = dict()
        for student in self.__students:
            student_group = student.get_group()
            if all_groups.get(student_group) == None:
                all_groups[student_group] = 1
                continue
            if need_year_enrl == None:
                all_groups[student_group] += 1
            elif need_year_enrl == student.get_year_of_enrollment():
                all_groups[student_group] += 1
        return all_groups
    
    def __get_year_enrl_grp(self, group_year_of_study):
        year_enrl_grp = (self.__now_date.tm_year) % 100

        if self.__now_date.tm_mon <= 8:
            year_enrl_grp -= 1
        
        year_enrl_grp -= (int(group_year_of_study) - 1)

        return str(year_enrl_grp)
    
    def __prepare_line_for_csv(self, student):
        END_CELL = ','
        END_ROW = '\n'
        finished_line =\
            self.__name_department +\
                str(student.get_id()) + '@isu365.ru' +\
                    END_CELL
        
        fname_temp = student.get_first_name() +\
            " " + student.get_middle_name()

        finished_line += fname_temp + END_CELL

        lname_temp = student.get_last_name()[0] + "***"

        finished_line += lname_temp + END_CELL
        finished_line += fname_temp + " " + lname_temp + END_CELL
        finished_line += 'студент' + END_CELL

        group_temp = student.get_group()

        if not group_temp == "Не указана":

            year_enrl_grp = self.__get_year_enrl_grp(group_temp[2:3])
            finished_line += group_temp[:2] +\
            year_enrl_grp +\
                group_temp[3:] +\
                    END_CELL

        else:

            finished_line += group_temp + END_CELL
        
        finished_line += '0' + END_CELL + '0' + END_CELL +\
            '0' + END_CELL + '0' + END_CELL
        finished_line += student.get_email() + END_CELL
        finished_line += '0' + END_CELL
        finished_line += student.get_year_of_enrollment() + END_CELL
        finished_line += self.__name_department + END_CELL
        finished_line += student.get_email() + END_CELL
        finished_line += 'Россия' + END_ROW
        return str(finished_line)

    def __default_get_students_list(self, max_group_dimension, need_year_enrl = None):
        all_groups = list()
        group_dimension_now = 0
        temp_list = list()
        i = 1
        for student in self.__students:
            if (group_dimension_now == max_group_dimension):
                group_dimension_now = 0
                all_groups.append(temp_list)
                temp_list = list()

            if need_year_enrl == None:
                temp_list.append(student)
                group_dimension_now += 1
                i += 1
            elif student.get_year_of_enrollment() == need_year_enrl:
                temp_list.append(student)
                group_dimension_now += 1
                i += 1
        
        if not (group_dimension_now == max_group_dimension):
            all_groups.append(temp_list)
        return all_groups
    
    def __get_students_group_list(self, folder_path):
        all_groups_in_dict = dict()

        for group_name, count_studs_in_grp in self.__all_grps_name_count_stud.items():

            now_count_studs_in_grp_temp = 0

            for student in self.__students:

                if now_count_studs_in_grp_temp == count_studs_in_grp:
                    break

                student_grp = student.get_group()
                now_group = all_groups_in_dict.get(student_grp)

                if now_group == None:
                    all_groups_in_dict[student_grp] = list()
                    now_group = all_groups_in_dict.get(student_grp)

                if group_name == student_grp:
                    now_group.append(student)
                    now_count_studs_in_grp_temp += 1

        return all_groups_in_dict
    
    def __gen_group_list_file(self, folder_path):
        from transliterate import translit

        path_with_txt = path.join(
            folder_path,
            'groups.txt'
        )
        txt_file = open(path_with_txt, 'w')

        for name_grp, count_stud_in_grp in\
            self.__all_grps_name_count_stud.items():

            if not name_grp == 'Не указана':

                grp_email = name_grp[:2] +\
                    self.__get_year_enrl_grp(name_grp[2:3]) +\
                            name_grp[3:-2] +\
                                translit(name_grp[-2:].lower(), reversed=True) +\
                                    "@isu365.ru"

                in_file_str = name_grp
                in_file_str += "\t" + grp_email

            else:

                in_file_str = 'Группа ' +\
                    name_grp.lower()
            
            in_file_str += "\t" +\
                            'количество студентов в группе: ' +\
                                str(count_stud_in_grp)

            in_file_str += "\n"
            txt_file.write(in_file_str)


    def default_save_as_azure_csv_format(self, folder_path, need_year_enrl=None):
        all_groups = self.__default_get_students_list(249, need_year_enrl)
        count_groups = len(all_groups)
        for i in range(count_groups):
            path_to_csv = path.join(
                folder_path,
                ('output' + str(i) + '.csv')
                )
            csv_file = open(path_to_csv, 'w')
            for student in all_groups[i]:
                csv_file.write(
                    self.__prepare_line_for_csv(student)
                )
        self.__gen_group_list_file(folder_path)
    
    def groups_save_as_azure_csv_format(self, folder_path, need_year_enrl = None):
        import time
        all_groups = self.__get_students_group_list(folder_path)
        for group_name, list_with_stud in all_groups.items():
            if not group_name == "Не указана":    
                path_to_csv = path.join(
                    folder_path,
                    ('output' + group_name + '.csv')
                )
            else:
                path_to_csv = path.join(
                    folder_path,
                    ('output' + " Группа " + group_name.lower() + '.csv')
                )
            csv_file = open(path_to_csv, 'w')
            for student in list_with_stud:
                csv_file.write(
                    self.__prepare_line_for_csv(student)
                )
        self.__gen_group_list_file(folder_path)